package io.framework.modules.water.utils;

import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import io.framework.common.utils.DateUtils;
import io.framework.modules.air.utils.AirFileCommonUtil;
import io.framework.modules.water.dto.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hwpf.HWPFDocument;
import org.apache.poi.hwpf.usermodel.*;
import org.apache.poi.hwpf.usermodel.Table;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xwpf.usermodel.*;
import org.springframework.beans.BeanUtils;

import java.io.FileInputStream;
import java.math.BigDecimal;
import java.util.*;

/**
 * 〈空气数据  上传文件〉
 *
 * @Author GJQ
 * @Create 2020/5/20
 * @Since 1.0.0
 */


public class WaterFileCommonUtil {

    public static void main(String[] args) {
        String filePath = "E:\\workspace\\projects_rongmo\\资料\\三亚环保项目\\Aaa第二版-新数据\\环保局数据\\水环境数据-综合室2020-05-29\\三亚市重点治理城镇内河（湖）水质状况\\";
        filePath += "2020年3月三亚市重点治理城镇内河（湖）水质状况.doc";

       /* List<WaterCityRiverDTO> list = new ArrayList<>();
        //获取第一个表格数据
        List<WaterCityRiverDTO> list1= WaterFileCommonUtil.getWaterDTOInfo3(filePath, "2020-03", 1);
        list.addAll(list1);
        System.out.println("11111111"+list1.toString());
        List<WaterCityRiverDTO> list2 =WaterFileCommonUtil.getWaterDTOInfo3(filePath, "2020-03", 2);
        //获取第二个表格数据
        list.addAll(list2);
        System.out.println("22222222"+list2);
        System.out.println(list.toString());
        System.out.println(list.size());*/

        String url = "D:\\ChoremDownLoad\\SY00站点(2020-06-21-2020-07-21)-(日-平均值)报表统计信息.xls";
        getOceanPointInfo("SY00", url);
    }

    //表格总数
    private static final Integer WATER_TABLE_TOTAL = 10;

    /**
     * 城镇内河(湖)水环境主要监测结果
     *
     * @param filePath
     * @param month
     * @param set
     * @return
     */
    public static List<WaterMonitorDTO> getWaterDTOInfo4(String filePath, String month, int set) {
        //获取第表格数据
        List<Map<String, Object>> mapList = readWordData(filePath, set, WATER_TABLE_TOTAL, 2, 1);
        List<WaterMonitorDTO> list = new ArrayList<>();
        WaterMonitorDTO dto = null;
        for (int i = 0; i < mapList.size(); i++) {
            dto = new WaterMonitorDTO();
            Map<String, Object> map = mapList.get(i);
            int k = 1;
            dto.setName(getStringValue(map, k++));
            dto.setStationName(getStringValue(map, k++));
            String year = getStringValue(map, k++);
            String mon = getStringValue(map, k++);
            String day = getStringValue(map, k++);
            String monitorDate = year + "-" + mon + "-" + day;
            dto.setMonitorDate(DateUtils.parse(monitorDate, DateUtils.DATE_PATTERN));
            //set代表第几个表格  1-海水河流型 2-湖库型 3-淡水河流型
            if (set == 1) {
                dto.setYd(getBigDecimalValue(map, k++));
                dto.setCw(Integer.parseInt(map.get(k++ + "").toString()));
                dto.setPh(getBigDecimalValue(map, k++));
                dto.setDoo2(getBigDecimalValue(map, k++, "＞"));
                dto.setCod(getBigDecimalValue(map, k++));
                dto.setH2po4(getBigDecimalValue(map, k++));
                dto.setOil(getBigDecimalValue(map, k++, "L"));
                dto.setNmin(getBigDecimalValue(map, k++));
            } else if (set == 2) {
                dto.setPh(getBigDecimalValue(map, k++));
                dto.setDoo2(getBigDecimalValue(map, k++, "＞"));
                dto.setK2mno4(getBigDecimalValue(map, k++));
                dto.setCod(getBigDecimalValue(map, k++));
                dto.setOil(getBigDecimalValue(map, k++, "L"));
                dto.setNh3(getBigDecimalValue(map, k++));
                dto.setTp(getBigDecimalValue(map, k++));
                dto.setTn(getBigDecimalValue(map, k++));
            } else if (set == 3) {
                dto.setPh(getBigDecimalValue(map, k++));
                dto.setDoo2(getBigDecimalValue(map, k++, "＞"));
                dto.setK2mno4(getBigDecimalValue(map, k++));
                dto.setCod(getBigDecimalValue(map, k++));
                dto.setOil(getBigDecimalValue(map, k++, "L"));
                dto.setNh3(getBigDecimalValue(map, k++));
                dto.setTn(getBigDecimalValue(map, k++));
            }
            dto.setNowLevel(getStringValue(map, k++));
            dto.setTargetLevel(getStringValue(map, k++));
            String passStr = getStringValue(map, k);
            Integer pass = 3;
            if ("是".equals(passStr)) {
                pass = 1;
            } else if ("否".equals(passStr)) {
                pass = 2;
            }
            dto.setPass(pass);
            dto.setType(set);
            dto.setMonth(DateUtils.parse(month, DateUtils.DATE_MONTH_PATTERN));
            dto.setDrRemarks(map.get("remarks").toString().trim());
            list.add(dto);
        }
        return list;
    }

    /**
     * 重点治理城镇内河（湖）水质状况
     *
     * @param filePath
     * @param month
     * @return
     */
    public static List<WaterCityRiverDTO> getWaterDTOInfo3(String filePath, String month, int set) {
        //获取第表格数据
        List<Map<String, Object>> mapList = readWordData(filePath, set, WATER_TABLE_TOTAL, 2, 1);
        List<WaterCityRiverDTO> list = new ArrayList<>();
        String name = "";
        WaterCityRiverDTO dto = null;
        for (int i = 0; i < mapList.size(); i++) {
            dto = new WaterCityRiverDTO();
            Map<String, Object> map = mapList.get(i);
            int k = 1;
            String code = getStringValue(map, k++);
            dto.setCode(code);
            if (StringUtils.isEmpty(code) || !code.contains("NH")) {
                continue;
            }
            if (StringUtils.isNotBlank(getStringValue(map, k))) {
                name = getStringValue(map, k);
            }
            k++;
            dto.setName(name);
            System.out.println(getStringValue(map, k));
            dto.setStationName(getStringValue(map, k++));
            dto.setLevel2017(getStringValue(map, k++));
            dto.setTargetLevel(getStringValue(map, k++));
            dto.setTongbiLevel(getStringValue(map, k++));
            dto.setHuanbiLevel(getStringValue(map, k++));
            dto.setNowLevel(getStringValue(map, k++));
            dto.setPassRemark(getStringValue(map, k++));
            dto.setLevel(getStringValue(map, k++));
            dto.setNotpassNdChange(getStringValue(map, k));
            dto.setMonth(DateUtils.parse(month, DateUtils.DATE_MONTH_PATTERN));
            dto.setDrRemarks(map.get("remarks").toString().trim());
            list.add(dto);
        }

        return list;
    }

    /**
     * 地表水常规监测水质类别表  第二个表格数据
     *
     * @param filePath
     * @param type
     * @param month
     * @return
     */
    public static List<WaterFractureSurfaceDTO> getWaterDTOInfo2(String filePath, int type, String month, int set) {
        //获取第二个表格数据
        List<Map<String, Object>> mapList = readWordData(filePath, set, WATER_TABLE_TOTAL, 1, 1);
        List<WaterFractureSurfaceDTO> list = new ArrayList<>();
        String yearPassRate = "";
        WaterFractureSurfaceDTO dto = null;
        for (int i = 0; i < mapList.size(); i++) {
            dto = new WaterFractureSurfaceDTO();
            Map<String, Object> map = mapList.get(i);
            int k = 1;
            dto.setName(getStringValue(map, k++));
            dto.setTargetLevel(getStringValue(map, k++));
            dto.setYearAvg(getStringValue(map, k++));
            dto.setPass(getStringValue(map, k++));
            dto.setNotpass(getStringValue(map, k++));
            if (i == 0) {
                yearPassRate = getStringValue(map, k);
            }
            dto.setYearPassRate(yearPassRate);
            dto.setMonth(DateUtils.parse(month, DateUtils.DATE_YEAR_PATTERN));
            dto.setDrRemarks(map.get("remarks").toString().trim());
            list.add(dto);
        }
        return list;
    }

    /**
     * 获取水质类别 信息  获取第一个表格数据  全年平均城市集中式饮用水源地水质类别表/地表水常规监测水质类别表
     *
     * @param filePath
     * @return
     */
    public static List<WaterDTO> getWaterDTOInfo1(String filePath, int type, String month, int set) {
        //获取第一个表格数据
        List<Map<String, Object>> mapList = readWordData(filePath, set, WATER_TABLE_TOTAL, 1, 1);
        List<WaterDTO> list = new ArrayList<>();
        String yearPassRate = "";
        for (Map<String, Object> map : mapList) {
            for (int i = 2; i <= 13; i++) {
                String monthClassify = getStringValue(map, i);
                if (StringUtils.isNotBlank(monthClassify)) {
                    WaterDTO dto = new WaterDTO();
                    dto.setName(getStringValue(map, 1));
                    dto.setYearAvg(getStringValue(map, 14));
                    if (StringUtils.isNotBlank(getStringValue(map, 15))) yearPassRate = getStringValue(map, 15);
                    dto.setPassRate(yearPassRate);
                    dto.setType(type);
                    String monthNow = (i - 1) + "";
                    dto.setMonth(DateUtils.parse(month.substring(0, month.indexOf("-")) + "-" + (monthNow.length() == 1 ? "0" + monthNow : monthNow), DateUtils.DATE_MONTH_PATTERN));
                    dto.setDrRemarks(map.get("remarks").toString().trim());
                    dto.setMonthNow(monthNow);
                    dto.setMonthClassify(monthClassify);
                    list.add(dto);
                }
            }
        }
        return list;
    }

    /**
     * 海洋浮标点
     *
     * @param stationCode
     * @param filePath
     * @return
     */
    public static List<OceanBuoyPointDTO> getOceanPointInfo(String stationCode, String filePath) {
        Workbook workbook = AirFileCommonUtil.readExcel(filePath);
        List<OceanBuoyPointDTO> oceanPointDTOs = null;
        if (workbook != null) {
            oceanPointDTOs = new ArrayList<>();
            //读取第一个表格
            Sheet sheet = workbook.getSheetAt(0);
            int rown = sheet.getPhysicalNumberOfRows();
            Row row = null;
            for (int i = 1; i < rown; i++) {
                if (i < 3) {
                    continue;
                }
                row = sheet.getRow(i);
                if (row != null) {
                    OceanBuoyPointDTO data = null;
                    if ("SY00".equals(stationCode) || "SY01".equals(stationCode)) {
                        data = setOceanPointInfoSY00(row);
                    } else if ("SY02".equals(stationCode) || "SY03".equals(stationCode) || "SY05".equals(stationCode)) {
                        data = setOceanPointInfoSY02(row);
                    } else if ("SY04".equals(stationCode)) {
                        data = setOceanPointInfoSY04(row);
                    }
                    if (data != null) {
                        //站点编码
                        data.setStationCode(stationCode);
                        oceanPointDTOs.add(data);
                    }
                }
            }
        }
        return oceanPointDTOs;
    }

    /**
     * 赋值 浮标点监测信息  code -> SY00 SY01
     *
     * @param row
     * @return
     */
    public static OceanBuoyPointDTO setOceanPointInfoSY00(Row row) {
        OceanBuoyPointDTO data = new OceanBuoyPointDTO();
        int i = 0;
        //时间
        data.setDate(DateUtils.parse(getCellFormatValue(row.getCell(i++)).toString(), DateUtils.DATE_PATTERN));
        //温度
        data.setWd((BigDecimal) getCellFormatValue(row.getCell(i++)));
        //盐度
        data.setYd((BigDecimal) getCellFormatValue(row.getCell(i++)));
        //电导率(mS/cm)
        data.setDdlv((BigDecimal) getCellFormatValue(row.getCell(i++)));
        //风速(m/s)
        data.setWindSpeed((BigDecimal) getCellFormatValue(row.getCell(i++)));
        //风向(°)
        data.setWindDirection((BigDecimal) getCellFormatValue(row.getCell(i++)));
        //最大瞬时风速(m/s)
        data.setMaxNowSpeed((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 最大瞬时风速风向(°)
        data.setMaxNowDirection((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 气温(℃)
        data.setAirTemperature((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 气压(hPa)
        data.setAirPressure((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 波个数
        data.setWaveNumber((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 平均波高(m)
        data.setWaveHeightAvg((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 平均波周期(s)
        data.setWavePeriodAvg((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 最大波高(m)
        data.setWaveHeightMax((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 最大波波周期(s)
        data.setWavePeriodMax((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 有效波高(m)
        data.setWaveHeightUseful((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 有效周期(s)
        data.setPeriodUseful((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 1/10H波高(m)
        data.setWaveHeightH((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 1/10T波高(m)
        data.setWaveHeightT((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 波向(°)
        data.setWaveDirection((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 流速(cm/s)
        data.setFlowRate((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 流向(°)
        data.setFlowDirection((BigDecimal) getCellFormatValue(row.getCell(i)));
        return data;
    }


    /**
     * 赋值 浮标点监测信息  code -> SY02 SY03 SY05
     *
     * @param row
     * @return
     */
    public static OceanBuoyPointDTO setOceanPointInfoSY02(Row row) {
        OceanBuoyPointDTO data = new OceanBuoyPointDTO();
        int i = 0;
        //时间
        data.setDate(DateUtils.parse(getCellFormatValue(row.getCell(i++)).toString(), DateUtils.DATE_PATTERN));
        //温度
        data.setWd((BigDecimal) getCellFormatValue(row.getCell(i++)));
        //盐度
        data.setYd((BigDecimal) getCellFormatValue(row.getCell(i++)));
        //溶解氧(mg/L)
        data.setDoo((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 溶解氧饱和度(%)
        data.setDoSaturation((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // PH
        data.setPh((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 叶绿素(μg/L)
        data.setChlorophyll((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 浊度(NTU)
        data.setCorrosion((BigDecimal) getCellFormatValue(row.getCell(i++)));
        //电导率(mS/cm)
        data.setDdlv((BigDecimal) getCellFormatValue(row.getCell(i)));

        return data;
    }

    /**
     * 赋值 浮标点监测信息  code -> SY04
     *
     * @param row
     * @return
     */
    public static OceanBuoyPointDTO setOceanPointInfoSY04(Row row) {
        OceanBuoyPointDTO data = new OceanBuoyPointDTO();
        int i = 0;
        //时间
        data.setDate(DateUtils.parse(getCellFormatValue(row.getCell(i++)).toString(), DateUtils.DATE_PATTERN));
        //温度
        data.setWd((BigDecimal) getCellFormatValue(row.getCell(i++)));
        //盐度
        data.setYd((BigDecimal) getCellFormatValue(row.getCell(i++)));
        //溶解氧(mg/L)
        data.setDoo((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 溶解氧饱和度(%)
        data.setDoSaturation((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // PH
        data.setPh((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 叶绿素(μg/L)
        data.setChlorophyll((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 浊度(NTU)
        data.setCorrosion((BigDecimal) getCellFormatValue(row.getCell(i++)));
        //电导率(mS/cm)
        data.setDdlv((BigDecimal) getCellFormatValue(row.getCell(i)));
        //氨氮(μg/L)
        data.setNh3((BigDecimal) getCellFormatValue(row.getCell(i)));
        // 硝氮(μg/L)
        data.setXd((BigDecimal) getCellFormatValue(row.getCell(i)));
        // 亚硝氮(μg/L)
        data.setYxd((BigDecimal) getCellFormatValue(row.getCell(i)));
        // 磷酸盐(μg/L)
        data.setH3po4((BigDecimal) getCellFormatValue(row.getCell(i)));
        // 波个数
        data.setWaveNumber((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 平均波高(m)
        data.setWaveHeightAvg((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 平均波周期(s)
        data.setWavePeriodAvg((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 最大波高(m)
        data.setWaveHeightMax((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 最大波波周期(s)
        data.setWavePeriodMax((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 有效波高(m)
        data.setWaveHeightUseful((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 有效周期(s)
        data.setPeriodUseful((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 1/10H波高(m)
        data.setWaveHeightH((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 1/10T波高(m)
        data.setWaveHeightT((BigDecimal) getCellFormatValue(row.getCell(i++)));
        // 波向(°)
        data.setWaveDirection((BigDecimal) getCellFormatValue(row.getCell(i)));

        return data;
    }

    /**
     * 滨海旅游区
     *
     * @param filePath
     * @param month
     * @param set
     * @return
     */
    public static List<WaterBinhaiDTO> getWaterBinhaiData(String filePath, String month, int set) {

        List<Map<String, Object>> mapList = readWordData(filePath, set, WATER_TABLE_TOTAL, 2, 1);
        List<WaterBinhaiDTO> list = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(mapList)) {
            mapList.forEach(map -> {
                int k = 1;
                WaterBinhaiDTO dto = new WaterBinhaiDTO();
                dto.setName(getStringValue(map, k++));
                dto.setFirstQuarter(getStringValue(map, k++));
                dto.setSecondQuarter(getStringValue(map, k++));
                dto.setThirdQuarter(getStringValue(map, k++));
                dto.setFourthQuarter(getStringValue(map, k));
                dto.setMonth(DateUtils.parse(month, DateUtils.DATE_YEAR_PATTERN));
                dto.setDrRemarks(map.get("remarks").toString().trim());
                list.add(dto);
            });
        }
        return list;
    }

    /**
     * 近岸海域海水
     *
     * @param filePath
     * @param month
     * @param set
     * @return
     */
    public static List<WaterNearshoreDTO> getWaterNearShoreData(String filePath, String month, int set) {

        List<Map<String, Object>> mapList = readWordData(filePath, set, WATER_TABLE_TOTAL, 2, 1);
        List<WaterNearshoreDTO> list = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(mapList)) {
            mapList.forEach(map -> {
                int k = 1;
                WaterNearshoreDTO dto = new WaterNearshoreDTO();
                dto.setName(getStringValue(map, k++));
//                dto.setCollectionPoint(getStringValue(map, k++));
                dto.setTargetClassify(getStringValue(map, k++));
                dto.setMonitorClassify(getStringValue(map, k++));
                dto.setTargetManagement(getStringValue(map, k++));
                dto.setTargetManagementBetter(getStringValue(map, k++));
                dto.setNotPass(getStringValue(map, k++));
                dto.setCollectionDate(DateUtils.parse(getStringValue(map, k), DateUtils.DATE_YEAR_PATTERN));
                dto.setMonth(DateUtils.parse(month, DateUtils.DATE_MONTH_PATTERN));
                dto.setDrRemarks(map.get("remarks").toString().trim());
                list.add(dto);
            });
        }
        return list;
    }

    /**
     * 读取文档中表格
     *
     * @param filePath  路径
     * @param set       如果有多个表格只读取需要的一个 set是设置需要读取的第几个表格，
     * @param total     过滤多余表格
     * @param startRows 开始取数据行
     * @param startCols 开始取数据列
     */
    public static List<Map<String, Object>> readWordData(String filePath, int set, Integer total, int startRows, int startCols) {
        try {
            List<Map<String, Object>> mapList = new ArrayList<>();
            FileInputStream in = new FileInputStream(filePath);//载入文档
            // 处理docx格式 即office2007以后版本
            if (filePath.toLowerCase().endsWith("docx")) {
                mapList = readWordDocx(in, set, total, startRows, startCols);
            } else {
                mapList = readWordDoc(in, set, total, startRows, startCols);
            }
            return mapList;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 读取  Word   Docx
     *
     * @param in
     * @param set
     * @param total
     * @param startRows
     * @param startCols
     * @return
     */
    public static List<Map<String, Object>> readWordDocx(FileInputStream in, int set, Integer total, int startRows, int startCols) {
        try {
            List<Map<String, Object>> mapList = new ArrayList<>();
            //word 2007 图片不会被读取， 表格中的数据会被放在字符串的最后
            XWPFDocument xwpf = new XWPFDocument(in);//得到word文档的信息
            List<XWPFParagraph> paragraphs = xwpf.getParagraphs();
            String remarks = "";
            int count = 0;
            for (XWPFParagraph paragraph : paragraphs) {
                String text = paragraph.getText();
                if (text.indexOf("注：") == 0) {
                    count++;
                    if (count == set) {
                        remarks = text;
                    }
                }
            }
            Iterator<XWPFTable> it = xwpf.getTablesIterator();//得到word中的表格

            int num = set;
            // 过滤前面不需要的表格
            for (int i = 0; i < set - 1; i++) {
                if (it.hasNext()) {
                    it.next();
                }
            }
            while (it.hasNext()) {
                XWPFTable table = it.next();
                System.out.println("这是第" + num + "个表的数据");
                List<XWPFTableRow> rows = table.getRows();
                //读取每一行数据
                for (int i = startRows; i < rows.size(); i++) {
                    XWPFTableRow row = rows.get(i);
                    //读取每一列数据
                    List<XWPFTableCell> cells = row.getTableCells();
                    Map<String, Object> map = new LinkedHashMap<>();
                    for (int j = startCols; j < cells.size(); j++) {
                        XWPFTableCell cell = cells.get(j);
                        //输出当前的单元格的数据
                        map.put(j + "", cell.getText());
                    }
                    map.put("remarks", remarks);
                    mapList.add(map);
                }
                // 过滤多余的表格
                if (total != null) {
                    while (num < total) {
                        if (it.hasNext()) {
                            it.next();
                        }
                        num++;
                    }
                }
            }
            return mapList;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 读取  Word  doc
     *
     * @param in
     * @param set
     * @param total
     * @param startRows
     * @param startCols
     * @return
     */
    public static List<Map<String, Object>> readWordDoc(FileInputStream in, int set, Integer total, int startRows, int startCols) {
        try {
            List<Map<String, Object>> mapList = new ArrayList<>();
            // 处理doc格式 即office2003版本
            POIFSFileSystem pfs = new POIFSFileSystem(in);
            HWPFDocument hwpf = new HWPFDocument(pfs);
            Range range = hwpf.getRange();//得到文档的读取范围
            TableIterator it = new TableIterator(range);
            String remarks = "";
            int count = 0;
            for (int i = 0; i < range.numParagraphs(); i++) {
                Paragraph paragraph = range.getParagraph(i);
                String text = paragraph.text();
                if (text.indexOf("注：") == 0) {
                    count++;
                    if (count == set) {
                        remarks = text;
                    }
                }
            }
            // 迭代文档中的表格
            int num = set;
            for (int i = 0; i < set - 1; i++) {
                if (it.hasNext()) {
                    it.next();
                }
            }
            while (it.hasNext()) {
                org.apache.poi.hwpf.usermodel.Table tb = (Table) it.next();
                //迭代行，默认从0开始,可以依据需要设置i的值,改变起始行数，也可设置读取到那行，只需修改循环的判断条件即可
                for (int i = startRows; i < tb.numRows(); i++) {
                    TableRow tr = tb.getRow(i);
                    Map<String, Object> map = new LinkedHashMap<>();
                    //迭代列，默认从0开始
                    for (int j = startCols; j < tr.numCells(); j++) {
                        TableCell td = tr.getCell(j);//取得单元格
                        //取得单元格的内容
                        for (int k = 0; k < td.numParagraphs(); k++) {
                            Paragraph para = td.getParagraph(k);
                            String s = para.text();
                            //去除后面的特殊符号
                            if (null != s && !"".equals(s)) {
                                s = s.substring(0, s.length() - 1);
                            }
                            map.put(j + "", s);
                        }
                    }
                    map.put("remarks", remarks);
                    mapList.add(map);
                }
                // 过滤多余的表格
                if (total != null) {
                    while (num < total) {
                        if (it.hasNext()) {
                            it.next();
                        }
                        num++;
                    }
                }
            }
            return mapList;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static String getStringValue(Map<String, Object> map, Integer k) {
        if (map.containsKey(k + ""))
            return map.get(k + "").toString().replaceAll(" ", "");
        else
            return null;
    }

    public static BigDecimal getBigDecimalValue(Map<String, Object> map, Integer k) {
        if (map.containsKey(k + "")) {
            String data = map.get(k + "").toString().replaceAll(" ", "");
            return new BigDecimal(data);
        } else
            return null;
    }

    public static BigDecimal getBigDecimalValue(Map<String, Object> map, Integer k, String regex) {
        if (map.containsKey(k + "")) {
            String data = map.get(k + "").toString().replaceAll(" ", "").replaceAll(regex, "");
            return new BigDecimal(data);
        } else
            return null;
    }

    public static String getStringValue(Map<String, Object> map, Integer k, String regex) {
        if (map.containsKey(k + ""))
            return map.get(k + "").toString().replaceAll(" ", "").replaceAll(regex, "");
        else
            return null;
    }


    public static Object getCellFormatValue(Cell cell) {
        Object cellValue = null;
        if (cell != null) {
            // 如果是字符串，直接获取值
            CellType cellType = cell.getCellTypeEnum();
            if (cellType.equals(CellType.STRING)) {
                cellValue = cell.getStringCellValue().replaceAll(" ", "");
            } else if (cellType.equals(CellType.NUMERIC)) {
                if (DateUtil.isCellDateFormatted(cell)) {
                    //转换为日期格式YYYY-mm-dd
                    cellValue = cell.getDateCellValue();
                } else {
                    //数字
                    cellValue = cell.getNumericCellValue();
                    if (cellValue instanceof Integer) {
                        cellValue = new BigDecimal((Integer) cellValue);
                    } else {
                        cellValue = new BigDecimal(cellValue + "");
                    }
                }
            }
            if ("NA".equals(cellValue)) {
                cellValue = new BigDecimal(0);
            }
        } else {
            cellValue = new BigDecimal(0);
        }
        return cellValue;
    }

}
